--
--		Analytics
--

create table public.analytics (
	id_analytics int4 not null,
	analytics_date date not null default current_date,
	constraint PK_ANALYTICS primary key(id_analytics)
);

create sequence public.analytics_seq;


alter table public.survey_answer_headers
	add id_analytics int4 null,
	add constraint FK_SURVEY_ANSWER_HEADER_ANALYTICS foreign key(id_analytics) references public.analytics;


create index survey_answer_headers_analytics_idx on survey_answer_headers(id_analytics);


----------------------------------------------------------------------------------
--					Time
----------------------------------------------------------------------------------

drop table if exists dim_survey_date;

create table dim_survey_date (
	id_date int4 not null,
	id_year int2 not null,
	id_month int2 not null,
	id_day int2 not null,
	fulldate date not null,
	id_organization int4 not null,
	constraint PK_DIM_SURVEY_DATE primary key(id_date, id_organization)
);


----------------------------------------------------------------------------------
--					Action origin
----------------------------------------------------------------------------------

-- This dimension is contained in the training_actions table
-- select id_action, action_code, action_description, id_organization from training_actions

create or replace view dim_origin_action as
select	id_action,
				action_code,
				action_name,
				action_description,
				id_organization
from training_actions;

----------------------------------------------------------------------------------
--					Student
----------------------------------------------------------------------------------

-- This is a DEGENERATE dimension  based on entity_id column of  survey_answer_headers;

----------------------------------------------------------------------------------
--					Geography
----------------------------------------------------------------------------------


create or replace view dim_geography as
select	p.id_province,
				p.province_name,
				cn.id_council,
				cn.council_name,
				op.id_organization
from councils cn, provinces p, organization_provinces op
where cn.id_province=p.id_province and p.id_province=op.id_province;


create or replace view dim_council_groups as
select
	cg.id_group,
	cg.group_name,
	cn.id_council,
	cn.council_name,
	cg.id_organization
from councils cn, council_groups cg, council_group_members cgm
where cn.id_council=cgm.id_council and cg.id_group=cgm.id_group;



----------------------------------------------------------------------------------
--					Surveys
----------------------------------------------------------------------------------

-- This dimension is contained in the surveys table
-- select id_survey, survey_title, id_organization from surveys;

create or replace view dim_survey as
select	id_survey,
				survey_title,
				id_organization
from surveys;

----------------------------------------------------------------------------------
--					Internet connection
----------------------------------------------------------------------------------

-- This is a DEGENERATE dimension  based on internet column of  survey_answer_headers;


----------------------------------------------------------------------------------
--					Training preference
----------------------------------------------------------------------------------

-- This dimension is contained in the training_preferences table
-- select id_training_preference, preference_description from training_preferences;

create or replace view dim_preference as
select	id_training_preference,
				preference_description
from training_preferences;


----------------------------------------------------------------------------------
--					Demanded action
----------------------------------------------------------------------------------


create or replace view dim_demanded_action as
select  
				t.id_action,
				t.action_code,
				t.action_name,
				t.action_description,
				tu.id_unit,
				null::varchar(50)  as unit_name,
				t.id_organization
from training_actions t, training_actions_units tu
where t.id_action=tu.id_action;


----------------------------------------------------------------------------------
--					Area
----------------------------------------------------------------------------------

create or replace view dim_area as
select	a.id_area,
				a.area_description,
				p.id_path,
				p.path_description,
				u.id_unit,
				null::varchar(50)  as unit_name,
				a.id_organization
from knowledge_areas a, knowledge_paths p, knowledge_path_units u
where a.id_area=p.id_area and p.id_path=u.id_path;




----------------------------------------------------------------------------------
--					Qualification
----------------------------------------------------------------------------------

create or replace view dim_qualification as
select	q.id_qualification,
				q.qualification_description,
				u.id_unit,
				null::varchar(50) as unit_name,
				q.id_organization
from qualifications q, qualification_units u
where q.id_qualification=u.id_qualification;



----------------------------------------------------------------------------------
--					Unit
----------------------------------------------------------------------------------

create or replace view dim_unit as
select	u.id_unit,
				u.unit_name,
				u.id_organization
from knowledge_units u;



----------------------------------------------------------------------------------
--      FACT TABLE  (preliminary)
----------------------------------------------------------------------------------


create table fact_survey_answers (
	id_organization int4 not null,
	id_unit int8 not null,
	id_date int4 not null,
	id_survey int4 not null,
	id_council varchar(6) not null,
	id_training_preference int4 not null,
	id_action int4 not null,
	entity_id varchar(9) not null,
	constraint PK_FACT_SURVEY_ANSWERS primary key(id_survey, id_unit, entity_id, id_date, id_council)
);


--
--	Process analytics procedure
--


create or replace function process_analytics() returns void as $$
declare
	the_analytics int4;
	the_analytics_date date;
	num_surveys int4;
begin
	-- Set date
	the_analytics_date := current_date;

	-- Get the number of surveys to archive
	select count(*) as cnt into num_surveys
	from survey_answer_headers h
	where h.id_analytics is null;

	if num_surveys > 0 then
			-- Generate archive
			the_analytics := nextval('analytics_seq');
      insert into analytics(id_analytics) values(the_analytics);

			-- Mark analytics
			update survey_answer_headers h set id_analytics=the_analytics where h.id_analytics is null;

			-- Process dim_survey_date
			insert into dim_survey_date(id_date, id_year, id_month, id_day, fulldate, id_organization)
			select
				date_to_id(h.creation_date)::int4 as id_date,
				extract(year from h.creation_date)::int2 as id_year,
				extract(month from h.creation_date)::int2 as id_month,
				extract(day from h.creation_date)::int2 as id_day,
				h.creation_date as fulldate,
				s.id_organization
			from survey_answer_headers h, surveys s
			where h.id_survey=s.id_survey and h.id_analytics = the_analytics and
			not exists (
				select 1 from dim_survey_date	d
				where d.fulldate=s.creation_date and d.id_organization=s.id_organization
			)
			group by h.creation_date, s.id_organization;



			-- Process fact_survey_answers
			insert into fact_survey_answers(id_organization, id_unit, id_date, id_survey, id_council, id_training_preference, id_action, entity_id)
			select  s.id_organization,
							q.id_unit,
							date_to_id(sh.creation_date)::int4 as id_date,
							sh.id_survey,
							sh.id_council,
							sa.training_preference as id_training_preference,
							sh.id_action,
							sh.entity_id
			from    survey_answer_headers sh,
							survey_unit_answers sa,
							questions q,
							surveys s
			where   sh.id_header=sa.id_header and
							sa.id_question=q.id_question and
							sh.id_survey=s.id_survey and sa.covered=false
							and sh.id_analytics = the_analytics;

	end if;


end;
$$ language plpgsql;